




Private Sub Worksheet_Change(ByVal Target As Range)
'    MsgBox Range("G1").MergeArea.Address
    Dim total_time As Double
    Dim total_time_row, total_time_column As Integer
    Dim total_time_column_letter As String
    Let total_time_column_letter = "A" 'constant
    Let total_time_column = ActiveSheet.Range(total_time_column_letter & 1).Column
    
    Dim case_count  As Integer
    Dim total_case_row, total_case_column As Integer
    Dim total_case_column_letter As String
    Let total_case_column_letter = "B" 'constant
    Let total_case_column = ActiveSheet.Range(total_case_column_letter & 1).Column
    
    Dim pass_count  As Integer
    Dim pass_row, pass_column As Integer
    Dim pass_column_letter As String
    Let pass_column_letter = "C" 'constant
    Let pass_column = ActiveSheet.Range(pass_column_letter & 1).Column
    
    
    Dim fail_count  As Integer
    Dim fail_row, fail_column As Integer
    Dim fail_column_letter As String
    Let fail_column_letter = "D" 'constant
    Let fail_column = ActiveSheet.Range(fail_column_letter & 1).Column
    
    Dim ongoing_count  As Integer
    Dim ongoing_row, ongoing_column As Integer
    Dim ongoing_column_letter As String
    Let ongoing_column_letter = "E" 'constant
    Let ongoing_column = ActiveSheet.Range(ongoing_column_letter & 1).Column
    
    Dim plan_count As Integer
    Dim plan_row, plan_column As Integer
    Dim plan_column_letter As String
    Let plan_column_letter = "F" 'constant
    Let plan_column = ActiveSheet.Range(plan_column_letter & 1).Column

    Dim percent_column, percent_row As Integer
    Dim percent As Double
    Dim percent_column_letter As String
    Let percent_column_letter = "G" ' constant
    Let percent_column = ActiveSheet.Range(percent_column_letter & 1).Column
    
    Dim component_column, component_row As Integer
    Dim component_range As Range
    Dim component_column_letter As String
    Let component_column_letter = "H" ' constant
    Let component_column = ActiveSheet.Range(component_column_letter & 1).Column
    
    Dim case_name As String
    Dim case_row, case_column As Integer
    Dim case_column_letter As String
    Let case_column_letter = "N" ' constant
    Let case_column = ActiveSheet.Range(case_column_letter & 1).Column
    If Target.Column < case_column Then
        Exit Sub
    End If
    
    Dim time As Double
    Dim time_row, time_column As Integer
    
    
    Dim status As String
    Dim status_row, status_column As Integer
    Dim status_rows_count As Integer
    Dim start_status_row As Integer
    Dim end_status_row As Integer
    Dim start_status_column As Integer
    
    Dim value As Integer
    
    
    Let component_column = Range(component_column_letter & "2").Column
    Let component_row = ActiveSheet.Cells(Target.Row, Target.Column).Row
    
    Set component_range = ActiveSheet.Range(component_column_letter & component_row).MergeArea
    
    
    If Not component_range.MergeCells Then
        Exit Sub
    End If
    
    time_row = component_range.Row + 1
    time_column = ActiveSheet.Range(case_column_letter & time_row).Column
    

    status_rows_count = component_range.Rows.Count
    status_row = time_row + 1
    start_status_row = status_row
    end_status_row = start_status_row + status_rows_count - 3
    
    status_column = time_column
    start_status_column = status_column
    
    time = ActiveSheet.Cells(time_row, time_column).value
    For status_row = start_status_row To end_status_row Step 1
        ActiveSheet.Cells(status_row, total_time_column).value = 0
        ActiveSheet.Cells(status_row, total_case_column).value = 0
        
        ActiveSheet.Cells(status_row, pass_column).value = 0
        ActiveSheet.Cells(status_row, fail_column).value = 0
        ActiveSheet.Cells(status_row, ongoing_column).value = 0
        ActiveSheet.Cells(status_row, plan_column).value = 0
        
        total_time = 0
        case_count = 0
        pass_count = 0
        fail_count = 0
        ongoing_count = 0
        plan_count = 0
        
        status_column = time_column
        start_status_column = status_column
        status = ActiveSheet.Cells(status_row, start_status_column).value
        time = ActiveSheet.Cells(time_row, start_status_column).value
        
        Debug.Print "status_row:" & status_row
        While time <> 0
            Debug.Print "    status_column:" & status_column
            Debug.Print "    time:" & time
            Select Case status
            Case "PASS"
                ActiveSheet.Cells(status_row, pass_column).value = ActiveSheet.Cells(status_row, pass_column).value + 1
                total_time = total_time + ActiveSheet.Cells(time_row, status_column).value
            Case "FAIL"
                ActiveSheet.Cells(status_row, fail_column).value = ActiveSheet.Cells(status_row, fail_column).value + 1
                total_time = total_time + ActiveSheet.Cells(time_row, status_column).value
            Case "ongoing"
                ActiveSheet.Cells(status_row, ongoing_column).value = ActiveSheet.Cells(status_row, ongoing_column).value + 1
                total_time = total_time + ActiveSheet.Cells(time_row, status_column).value
            Case "plan"
                ActiveSheet.Cells(status_row, plan_column).value = ActiveSheet.Cells(status_row, plan_column).value + 1
                total_time = total_time + ActiveSheet.Cells(time_row, status_column).value
            Case Else    ' Other values.
                Debug.Print "Not in  {PASS, FAIL ongoing, plan}"
            End Select
            
             ActiveSheet.Cells(status_row, total_time_column).value = total_time
                                                                    
             ActiveSheet.Cells(status_row, total_case_column).value = ActiveSheet.Cells(status_row, pass_column).value _
                                                                    + ActiveSheet.Cells(status_row, fail_column).value _
                                                                    + ActiveSheet.Cells(status_row, ongoing_column).value _
                                                                    + ActiveSheet.Cells(status_row, plan_column).value

            If ActiveSheet.Cells(status_row, total_case_column).value <> 0 Then
                ActiveSheet.Cells(status_row, percent_column).value = (ActiveSheet.Cells(status_row, total_case_column).value - ActiveSheet.Cells(status_row, plan_column).value) / ActiveSheet.Cells(status_row, total_case_column).value * 100 & "%"
            Else
                 ActiveSheet.Cells(status_row, percent_column).value = 0 & "%"
            End If
            
            status_column = status_column + 1
            status = ActiveSheet.Cells(status_row, status_column).value
            time = ActiveSheet.Cells(time_row, status_column).value
        Wend
        
    Next status_row
    
    ActiveSheet.Cells(time_row, total_time_column).value = Application.WorksheetFunction.Sum(Range(total_time_column_letter & start_status_row & ":" & total_time_column_letter & end_status_row))
    ActiveSheet.Cells(time_row, total_case_column).value = Application.WorksheetFunction.Sum(Range(total_case_column_letter & start_status_row & ":" & total_case_column_letter & end_status_row))
    ActiveSheet.Cells(time_row, pass_column).value = Application.WorksheetFunction.Sum(Range(pass_column_letter & start_status_row & ":" & pass_column_letter & end_status_row))
    ActiveSheet.Cells(time_row, fail_column).value = Application.WorksheetFunction.Sum(Range(fail_column_letter & start_status_row & ":" & fail_column_letter & end_status_row))
    ActiveSheet.Cells(time_row, ongoing_column).value = Application.WorksheetFunction.Sum(Range(ongoing_column_letter & start_status_row & ":" & ongoing_column_letter & end_status_row))
    ActiveSheet.Cells(time_row, plan_column).value = Application.WorksheetFunction.Sum(Range(plan_column_letter & start_status_row & ":" & plan_column_letter & end_status_row))
    
'    MsgBox component_row
End Sub


